#coding=utf-8
from framework.db.mysql import BaseDb
from tornado import gen
from lib import log

class Teacher(BaseDb):

    # @gen.coroutine
    # def select_teacher(self, school_id, where, page, offset):
    #     sql = '''SELECT a.Fid, a.Fname, a.Fno, b.Fdorm_name, c.Fname Froom
    #             , a.Ftel, a.Fage, a.Favatar, a.Fstatus, a.Fcreate_time
    #             FROM tb_student a 
    #             LEFT JOIN tb_dorm b 
    #             ON a.Fdorm_id = b.Fid
    #             LEFT JOIN tb_room c 
    #             ON a.Froom = c.Fid
    #             WHERE a.Fschool_id = %s %s 
    #             LIMIT %s, %s'''
    #     count_sql = 'SELECT count(a.Fid) FROM tb_student a WHERE a.Fschool_id = %s %s'

    #     wheres = ''
    #     for i in where.keys():
    #         if where[i] != '':
    #             wheres += " AND a." + i + "='" + str(where[i]) + "'"
    #     sql = sql % ('%s', wheres, '%s', '%s')
    #     count_sql = count_sql % ('%s', wheres)

    #     ret = yield self.executejson(sql, (school_id, page, offset))
    #     count = yield self.execute(count_sql, (school_id))

    #     raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def insert_teacher(self, teacher_data, teacher_info):

        ret = yield self.insert("tb_teacher", teacher_info)
        teacher_data["Fuser_id"] = ret
        ret = yield self.insert("tb_user_login", teacher_data)

        self.commit()

        raise gen.Return(ret)

    @gen.coroutine
    def select_teacher(self, school_id, where, page, offset):
        sql = '''SELECT Fid, Fname, Ftel, Fmajor_id, Favatar
                , Faddress, Fage, Fremark, Fcreate_time
                FROM tb_teacher 
                WHERE Fschool_id = %s %s 
                ORDER BY Fcreate_time DESC
                LIMIT %s, %s'''
        count_sql = 'SELECT count(Fid) FROM tb_teacher WHERE Fschool_id = %s'

        wheres = ''
        for i in where.keys():
            if where[i] != '':
                wheres += " AND " + i + "='" + str(where[i]) + "'"
        sql = sql % ('%s', wheres, '%s', '%s')

        ret = yield self.executejson(sql, (school_id, page, offset))
        count = yield self.execute(count_sql, (school_id))

        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def find_teacher(self, Fid):
        sql = '''SELECT a.Fid, a.Fname, a.Ftel, a.Fmajor_id, a.Favatar
                , a.Faddress, a.Fage, a.Fage, a.Fremark, a.Fcreate_time
                , b.Fuser_name, b.Fpwd
                FROM tb_teacher a 
                LEFT JOIN tb_user_login b 
                ON b.Fuser_id = a.Fid
                WHERE a.Fid = %s '''

        ret = yield self.executejson(sql, (Fid))

        if len(ret) >= 1:
            raise gen.Return(ret[0])
        else:
            raise gen.Return({})

    @gen.coroutine
    def update_teacher(self, Fid, Fuser_name, arg):
        teacher_sql = '''UPDATE tb_teacher SET %s WHERE Fid = %s'''
        user_sql = 'UPDATE tb_user_login SET Fuser_name = %s WHERE Fuser_id = %s'

        update_data = ''
        for i in arg.keys():
            update_data += i + '=%s,'

        teacher_sql = teacher_sql % (update_data[0:-1], '%s')

        data = arg.values()
        data.append(Fid)

        ret = yield self.upsert(teacher_sql, tuple(data))
        user = yield self.upsert(user_sql, (Fuser_name, Fid))

        self.commit()

        raise gen.Return((ret, user))

    @gen.coroutine
    def del_teacher(self, Fid):
        sql = 'DELETE FROM tb_teacher WHERE Fid = %s'

        ret = yield self.upsert_commit(sql, (Fid))

        raise gen.Return(ret)